{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Reading and writing CSV files with NumPy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-01-05T11:16:58.324860Z",
     "start_time": "2021-01-05T11:16:58.242010Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[[14. 32. 33.]\n",
      " [24. 45. 26.]\n",
      " [27. 38. 39.]]\n"
     ]
    }
   ],
   "source": [
    "# import genfromtxt function\n",
    "from numpy import genfromtxt\n",
    "\n",
    "# Read comma separated file\n",
    "product_data = genfromtxt('demo.csv', delimiter=',')\n",
    "\n",
    "# display initial 5 records\n",
    "print(product_data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-01-05T11:16:59.120925Z",
     "start_time": "2021-01-05T11:16:59.108318Z"
    }
   },
   "outputs": [],
   "source": [
    "# import numpy\n",
    "import numpy as np\n",
    "\n",
    "# Create a sample array\n",
    "sample_array = np.asarray([ [1,2,3], [4,5,6], [7,8,9] ])\n",
    "\n",
    "# Write sample array to CSV file\n",
    "np.savetxt(\"my_first_demo.csv\", sample_array, delimiter=\",\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Reading and writing CSV files with Pandas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-01-05T11:17:04.189363Z",
     "start_time": "2021-01-05T11:16:59.769652Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>14</td>\n",
       "      <td>32</td>\n",
       "      <td>33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>24</td>\n",
       "      <td>45</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>27</td>\n",
       "      <td>38</td>\n",
       "      <td>39</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    0   1   2\n",
       "0  14  32  33\n",
       "1  24  45  26\n",
       "2  27  38  39"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# import pandas\n",
    "import pandas as pd\n",
    "\n",
    "# Read CSV file\n",
    "sample_df=pd.read_csv('demo.csv', sep=',' , header=None)\n",
    "\n",
    "# display initial 5 records\n",
    "sample_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-01-05T11:17:04.205214Z",
     "start_time": "2021-01-05T11:17:04.200972Z"
    }
   },
   "outputs": [],
   "source": [
    "# Save DataFrame to CSV file\n",
    "sample_df.to_csv('demo_sample_df.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Reading and Writing Data from Excel"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-01-05T11:17:04.570391Z",
     "start_time": "2021-01-05T11:17:04.219774Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>performance_score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Allen Smith</td>\n",
       "      <td>723</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>S Kumar</td>\n",
       "      <td>520</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Jack Morgan</td>\n",
       "      <td>674</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Ying Chin</td>\n",
       "      <td>556</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Dheeraj Patel</td>\n",
       "      <td>711</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            name  performance_score\n",
       "0    Allen Smith                723\n",
       "1        S Kumar                520\n",
       "2    Jack Morgan                674\n",
       "3      Ying Chin                556\n",
       "4  Dheeraj Patel                711"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Read excel file\n",
    "df=pd.read_excel('employee.xlsx',sheet_name='performance')\n",
    "\n",
    "# display initial 5 records\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-01-05T11:17:04.884401Z",
     "start_time": "2021-01-05T11:17:04.582194Z"
    }
   },
   "outputs": [],
   "source": [
    "df.to_excel('employee_performance.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-01-05T11:17:04.909980Z",
     "start_time": "2021-01-05T11:17:04.898999Z"
    }
   },
   "outputs": [],
   "source": [
    "# Read excel file\n",
    "emp_df=pd.read_excel('employee.xlsx',sheet_name='employee_details')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-01-05T11:17:04.966740Z",
     "start_time": "2021-01-05T11:17:04.925973Z"
    }
   },
   "outputs": [],
   "source": [
    "# write multiple dataframes to single excel file\n",
    "with pd.ExcelWriter('new_employee_details.xlsx') as writer:\n",
    "    emp_df.to_excel(writer, sheet_name='employee')\n",
    "    df.to_excel(writer, sheet_name='perfromance')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Reading and Writing Data from JSON"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-01-05T11:17:05.077512Z",
     "start_time": "2021-01-05T11:17:04.984825Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>age</th>\n",
       "      <th>income</th>\n",
       "      <th>gender</th>\n",
       "      <th>department</th>\n",
       "      <th>grade</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Allen Smith</td>\n",
       "      <td>45.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>Operations</td>\n",
       "      <td>G3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>S Kumar</td>\n",
       "      <td>NaN</td>\n",
       "      <td>16000.0</td>\n",
       "      <td>F</td>\n",
       "      <td>Finance</td>\n",
       "      <td>G0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Jack Morgan</td>\n",
       "      <td>32.0</td>\n",
       "      <td>35000.0</td>\n",
       "      <td>M</td>\n",
       "      <td>Finance</td>\n",
       "      <td>G2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Ying Chin</td>\n",
       "      <td>45.0</td>\n",
       "      <td>65000.0</td>\n",
       "      <td>F</td>\n",
       "      <td>Sales</td>\n",
       "      <td>G3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Dheeraj Patel</td>\n",
       "      <td>30.0</td>\n",
       "      <td>42000.0</td>\n",
       "      <td>F</td>\n",
       "      <td>Operations</td>\n",
       "      <td>G2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            name   age   income gender  department grade\n",
       "0    Allen Smith  45.0      NaN   None  Operations    G3\n",
       "1        S Kumar   NaN  16000.0      F     Finance    G0\n",
       "2    Jack Morgan  32.0  35000.0      M     Finance    G2\n",
       "3      Ying Chin  45.0  65000.0      F       Sales    G3\n",
       "4  Dheeraj Patel  30.0  42000.0      F  Operations    G2"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Reading JSON file\n",
    "df=pd.read_json('employee.json')\n",
    "\n",
    "# display initial 5 records\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-01-05T11:17:05.146276Z",
     "start_time": "2021-01-05T11:17:05.092267Z"
    }
   },
   "outputs": [],
   "source": [
    "# Writing DataFrame to JSON file\n",
    "df.to_json('employee_demo.json',orient=\"columns\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Reading and Writing Data from HDF5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-01-05T11:17:07.857869Z",
     "start_time": "2021-01-05T11:17:05.163801Z"
    }
   },
   "outputs": [],
   "source": [
    "# Write DataFrame to hdf5\n",
    "df.to_hdf('employee.h5', 'table', append=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-01-05T11:17:07.927541Z",
     "start_time": "2021-01-05T11:17:07.876078Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>age</th>\n",
       "      <th>income</th>\n",
       "      <th>gender</th>\n",
       "      <th>department</th>\n",
       "      <th>grade</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Allen Smith</td>\n",
       "      <td>45.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Operations</td>\n",
       "      <td>G3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>S Kumar</td>\n",
       "      <td>NaN</td>\n",
       "      <td>16000.0</td>\n",
       "      <td>F</td>\n",
       "      <td>Finance</td>\n",
       "      <td>G0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Jack Morgan</td>\n",
       "      <td>32.0</td>\n",
       "      <td>35000.0</td>\n",
       "      <td>M</td>\n",
       "      <td>Finance</td>\n",
       "      <td>G2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Ying Chin</td>\n",
       "      <td>45.0</td>\n",
       "      <td>65000.0</td>\n",
       "      <td>F</td>\n",
       "      <td>Sales</td>\n",
       "      <td>G3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Dheeraj Patel</td>\n",
       "      <td>30.0</td>\n",
       "      <td>42000.0</td>\n",
       "      <td>F</td>\n",
       "      <td>Operations</td>\n",
       "      <td>G2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            name   age   income gender  department grade\n",
       "0    Allen Smith  45.0      NaN    NaN  Operations    G3\n",
       "1        S Kumar   NaN  16000.0      F     Finance    G0\n",
       "2    Jack Morgan  32.0  35000.0      M     Finance    G2\n",
       "3      Ying Chin  45.0  65000.0      F       Sales    G3\n",
       "4  Dheeraj Patel  30.0  42000.0      F  Operations    G2"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Read a hdf5 file\n",
    "df=pd.read_hdf('employee.h5', 'table')\n",
    "\n",
    "# display initial 5 records\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Reading and Writing Data from HTML Tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-01-05T11:17:08.068910Z",
     "start_time": "2021-01-05T11:17:07.947823Z"
    }
   },
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-01-05T11:17:09.774371Z",
     "start_time": "2021-01-05T11:17:08.104866Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of DataFrames: 8\n"
     ]
    }
   ],
   "source": [
    "# Reading HTML table from given URL\n",
    "table_url = 'https://en.wikipedia.org/wiki/List_of_sovereign_states_and_dependent_territories_in_North_America'\n",
    "df_list = pd.read_html(table_url)\n",
    "print(\"Number of DataFrames:\",len(df_list))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-01-05T11:17:09.828493Z",
     "start_time": "2021-01-05T11:17:09.821301Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   Flag        English Short Name            English Long Name  \\\n",
      "0   NaN  Antigua and Barbuda[n 1]          Antigua and Barbuda   \n",
      "1   NaN         Bahamas, The[n 1]  Commonwealth of The Bahamas   \n",
      "2   NaN             Barbados[n 1]                     Barbados   \n",
      "3   NaN          Belize[n 1][n 2]                       Belize   \n",
      "4   NaN               Canada[n 3]                       Canada   \n",
      "\n",
      "          Domestic Short Name(s)     Capital               Currency  \n",
      "0   English: Antigua and Barbuda  St. John's  East Caribbean dollar  \n",
      "1               English: Bahamas      Nassau        Bahamian dollar  \n",
      "2              English: Barbados  Bridgetown       Barbadian dollar  \n",
      "3                English: Belize    Belmopan          Belize dollar  \n",
      "4  English: CanadaFrench: Canada      Ottawa        Canadian dollar  \n"
     ]
    }
   ],
   "source": [
    "# Check first DataFrame\n",
    "print(df_list[0].head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-01-05T11:17:09.904119Z",
     "start_time": "2021-01-05T11:17:09.870185Z"
    }
   },
   "outputs": [],
   "source": [
    "# Write DataFrame to raw HTML\n",
    "df_list[1].to_html('country.html')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Reading and Writing Data from parquet"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-01-05T11:17:19.638298Z",
     "start_time": "2021-01-05T11:17:10.361485Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: pyarrow in /home/avinash/anaconda3/lib/python3.8/site-packages (2.0.0)\n",
      "Requirement already satisfied: numpy>=1.14 in /home/avinash/anaconda3/lib/python3.8/site-packages (from pyarrow) (1.18.5)\n"
     ]
    }
   ],
   "source": [
    "!pip install pyarrow"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-01-05T11:17:21.210240Z",
     "start_time": "2021-01-05T11:17:19.686527Z"
    },
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# Write to a parquet file.\n",
    "df.to_parquet('employee.parquet', engine='pyarrow')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-01-05T11:17:22.198672Z",
     "start_time": "2021-01-05T11:17:21.284184Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>age</th>\n",
       "      <th>income</th>\n",
       "      <th>gender</th>\n",
       "      <th>department</th>\n",
       "      <th>grade</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Allen Smith</td>\n",
       "      <td>45.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>Operations</td>\n",
       "      <td>G3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>S Kumar</td>\n",
       "      <td>NaN</td>\n",
       "      <td>16000.0</td>\n",
       "      <td>F</td>\n",
       "      <td>Finance</td>\n",
       "      <td>G0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Jack Morgan</td>\n",
       "      <td>32.0</td>\n",
       "      <td>35000.0</td>\n",
       "      <td>M</td>\n",
       "      <td>Finance</td>\n",
       "      <td>G2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Ying Chin</td>\n",
       "      <td>45.0</td>\n",
       "      <td>65000.0</td>\n",
       "      <td>F</td>\n",
       "      <td>Sales</td>\n",
       "      <td>G3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Dheeraj Patel</td>\n",
       "      <td>30.0</td>\n",
       "      <td>42000.0</td>\n",
       "      <td>F</td>\n",
       "      <td>Operations</td>\n",
       "      <td>G2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            name   age   income gender  department grade\n",
       "0    Allen Smith  45.0      NaN   None  Operations    G3\n",
       "1        S Kumar   NaN  16000.0      F     Finance    G0\n",
       "2    Jack Morgan  32.0  35000.0      M     Finance    G2\n",
       "3      Ying Chin  45.0  65000.0      F       Sales    G3\n",
       "4  Dheeraj Patel  30.0  42000.0      F  Operations    G2"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Read parquet file\n",
    "employee_df = pd.read_parquet('employee.parquet', engine='pyarrow')\n",
    "\n",
    "# display initial 5 records\n",
    "employee_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Reading and Writing Data from Pickle Pandas Object"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-01-05T11:17:22.256763Z",
     "start_time": "2021-01-05T11:17:22.250886Z"
    }
   },
   "outputs": [],
   "source": [
    "# import pandas\n",
    "import pandas as pd\n",
    "\n",
    "# Read CSV file\n",
    "df=pd.read_csv('demo.csv', sep=',' , header=None)\n",
    "\n",
    "# Save DataFrame object in pickle file\n",
    "df.to_pickle('demo_obj.pkl')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-01-05T11:17:22.343540Z",
     "start_time": "2021-01-05T11:17:22.293917Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>14</td>\n",
       "      <td>32</td>\n",
       "      <td>33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>24</td>\n",
       "      <td>45</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>27</td>\n",
       "      <td>38</td>\n",
       "      <td>39</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    0   1   2\n",
       "0  14  32  33\n",
       "1  24  45  26\n",
       "2  27  38  39"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Read DataFrame object from pickle file\n",
    "pickle_obj=pd.read_pickle('demo_obj.pkl')\n",
    "\n",
    "# display initial 5 records\n",
    "pickle_obj.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Lightweight access with sqllite3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-01-05T11:17:31.746479Z",
     "start_time": "2021-01-05T11:17:31.546386Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[(105, 57000)]\n"
     ]
    }
   ],
   "source": [
    "# Import sqlite3\n",
    "import sqlite3\n",
    "\n",
    "# Create connection. This will create the connection with employee database. If the database does not exist it will create the database\n",
    "conn = sqlite3.connect('employee.db')\n",
    "\n",
    "# Create cursor\n",
    "cur = conn.cursor()\n",
    "\n",
    "# Execute SQL query and create the database table\n",
    "cur.execute(\"create table emp(eid int,salary int)\")\n",
    "\n",
    "# Execute SQL query and Write the data into database\n",
    "cur.execute(\"insert into emp values(105, 57000)\")\n",
    "\n",
    "# commit the transaction\n",
    "conn.commit()\n",
    "\n",
    "# Execute SQL query and Read the data from the database\n",
    "cur.execute('select * from emp')\n",
    "\n",
    "# Fetch records\n",
    "print(cur.fetchall())\n",
    "\n",
    "# Close the Database connection\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Reading and Writing Data from MySQL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-01-05T11:17:45.856194Z",
     "start_time": "2021-01-05T11:17:45.484837Z"
    }
   },
   "outputs": [],
   "source": [
    "# import pymysql connector module\n",
    "import pymysql\n",
    "\n",
    "# Create a connection object using connect() method \n",
    "\n",
    "connection = pymysql.connect(host='localhost', # IP address of the MySQL database server\n",
    "                             user='root', # user name\n",
    "                             password='root', # password\n",
    "                             db='emp', # database name\n",
    "                             charset='utf8mb4', # character set\n",
    "                             cursorclass=pymysql.cursors.DictCursor) # cursor type\n",
    "\n",
    "try:\n",
    "    with connection.cursor() as cur:\n",
    "        # Inject a record in database\n",
    "        sql_query = \"INSERT INTO `emp` (`eid`, `salary`) VALUES (%s, %s)\"\n",
    "        cur.execute(sql_query, (104,43000))\n",
    "\n",
    "\n",
    "    # Commit the record insertion explicitly.\n",
    "    connection.commit()\n",
    "\n",
    "    with connection.cursor() as cur:\n",
    "        # Read records from employee table\n",
    "        sql_query = \"SELECT * FROM `emp`\"\n",
    "        cur.execute(sql_query )\n",
    "        table_data = cur.fetchall()\n",
    "        print(table_data)\n",
    "except:\n",
    "    print(\"Exception Occurred\")\n",
    "finally:\n",
    "    connection.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-01-05T11:17:48.856725Z",
     "start_time": "2021-01-05T11:17:48.848824Z"
    }
   },
   "outputs": [],
   "source": [
    "# Import the required connector\n",
    "import mysql.connector\n",
    "import pandas as pd\n",
    "\n",
    "# Establish a database connection to mysql\n",
    "connection=mysql.connector.connect(user='root',password='root',host='localhost',database='emp')\n",
    "\n",
    "# Create a cursor\n",
    "cur=connection.cursor()\n",
    "\n",
    "# Running sql query\n",
    "cur.execute(\"select * from emp\")\n",
    "\n",
    "# Fetch all the records and print it one by one\n",
    "records=cur.fetchall()\n",
    "for i in records:\n",
    "    print(i)\n",
    "\n",
    "# Create a DataFrame from fetched records.\n",
    "df = pd.DataFrame(records)\n",
    "\n",
    "# Assign column names to DataFrame\n",
    "df.columns = [i[0] for i in cur.description]\n",
    "\n",
    "# close the connection\n",
    "connection.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-01-05T11:17:50.702212Z",
     "start_time": "2021-01-05T11:17:50.096171Z"
    }
   },
   "outputs": [],
   "source": [
    "# Import the sqlalchemy engine\n",
    "from sqlalchemy import create_engine\n",
    "\n",
    "# Instantiate engine object\n",
    "en = create_engine(\"mysql+pymysql://{user}:{pw}@localhost/{db}\"\n",
    "                .format(user=\"root\", pw=\"root\", \n",
    "                        db=\"emp\"))\n",
    "\n",
    "# Insert the whole dataframe into the database\n",
    "df.to_sql('emp', con=en, if_exists='append',chunksize=1000, index= False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Reading and Writing Data from MongoDB"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-01-04T10:26:04.773507Z",
     "start_time": "2021-01-04T10:26:00.975720Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Collecting pymongo\n",
      "  Downloading pymongo-3.11.2-cp38-cp38-manylinux2014_x86_64.whl (531 kB)\n",
      "\u001b[K     |████████████████████████████████| 531 kB 1.7 MB/s eta 0:00:01\n",
      "\u001b[?25hInstalling collected packages: pymongo\n",
      "Successfully installed pymongo-3.11.2\n"
     ]
    }
   ],
   "source": [
    "!pip install pymongo"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-01-05T12:00:10.414404Z",
     "start_time": "2021-01-05T12:00:07.833644Z"
    }
   },
   "outputs": [],
   "source": [
    "# Import pymongo\n",
    "import pymongo\n",
    "\n",
    "# Create mongo client\n",
    "client = pymongo.MongoClient()\n",
    "\n",
    "# Get database\n",
    "db = client.employee\n",
    "\n",
    "# Get the collection from database\n",
    "collection = db.emp\n",
    "\n",
    "# Write the data using insert_one() method\n",
    "employee_salary = {\"eid\":114, \"salary\":25000}\n",
    "collection.insert_one(employee_salary)\n",
    "\n",
    "# Create a dataframe with fetched data\n",
    "data = pd.DataFrame(list(collection.find()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-01-05T12:00:23.511935Z",
     "start_time": "2021-01-05T12:00:23.306811Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>_id</th>\n",
       "      <th>eid</th>\n",
       "      <th>salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5ff454ca3fdcce328b7f8fdb</td>\n",
       "      <td>114</td>\n",
       "      <td>25000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        _id  eid  salary\n",
       "0  5ff454ca3fdcce328b7f8fdb  114   25000"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Reading and Writing Data from Cassandra"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-01-05T11:17:58.635707Z",
     "start_time": "2021-01-05T11:17:56.824802Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: cassandra-driver in /home/avinash/anaconda3/lib/python3.8/site-packages (3.24.0)\n",
      "Requirement already satisfied: geomet<0.3,>=0.1 in /home/avinash/anaconda3/lib/python3.8/site-packages (from cassandra-driver) (0.2.1.post1)\n",
      "Requirement already satisfied: six>=1.9 in /home/avinash/anaconda3/lib/python3.8/site-packages (from cassandra-driver) (1.15.0)\n",
      "Requirement already satisfied: click in /home/avinash/anaconda3/lib/python3.8/site-packages (from geomet<0.3,>=0.1->cassandra-driver) (7.1.2)\n"
     ]
    }
   ],
   "source": [
    "!pip install cassandra-driver"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-01-05T11:31:17.991716Z",
     "start_time": "2021-01-05T11:31:17.907335Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "101 Steve Smith 42\n"
     ]
    }
   ],
   "source": [
    "# Import the cluster\n",
    "from cassandra.cluster import Cluster\n",
    "\n",
    "# Creating a cluster object\n",
    "cluster = Cluster()\n",
    "\n",
    "# Create connections by calling Cluster.connect():\n",
    "conn = cluster.connect()\n",
    "\n",
    "# Execute the insert query\n",
    "conn.execute(\"\"\"INSERT INTO employee.emp_details (eid, ename, age) VALUES (%(eid)s, %(ename)s, %(age)s)\"\"\", {'eid':101, 'ename': \"Steve Smith\", 'age': 42})\n",
    "\n",
    "# Execute the select query\n",
    "rows = conn.execute('SELECT * FROM employee.emp_details')\n",
    "\n",
    "# Print the results\n",
    "for emp_row in rows:\n",
    "    print(emp_row.eid, emp_row.ename, emp_row.age)\n",
    "\n",
    "# Create a dataframe with fetched data\n",
    "data = pd.DataFrame(rows)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Reading and Writing Data from Redis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-01-05T11:36:31.211594Z",
     "start_time": "2021-01-05T11:36:06.817328Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: redis in /home/avinash/anaconda3/lib/python3.8/site-packages (3.5.3)\r\n"
     ]
    }
   ],
   "source": [
    "!pip install redis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-01-05T11:36:04.459861Z",
     "start_time": "2021-01-05T11:36:03.833339Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "b'101'\n"
     ]
    }
   ],
   "source": [
    "# Import module\n",
    "import redis\n",
    "\n",
    "# Create connection\n",
    "r = redis.Redis(host='localhost', port=6379, db=0)\n",
    "\n",
    "# Setting key-value pair\n",
    "r.set('eid', '101')\n",
    "\n",
    "# Get value for given key\n",
    "value=r.get('eid')\n",
    "\n",
    "# Print the value\n",
    "print(value)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Pony ORM"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-01-04T10:07:12.283690Z",
     "start_time": "2021-01-04T10:07:07.675Z"
    }
   },
   "outputs": [],
   "source": [
    "!pip install pony"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-01-05T11:22:44.859343Z",
     "start_time": "2021-01-05T11:22:44.377088Z"
    }
   },
   "outputs": [],
   "source": [
    "# Import pony module\n",
    "from pony.orm import *\n",
    "\n",
    "# Create database\n",
    "db = Database()\n",
    "\n",
    "# Define entities\n",
    "class Emp(db.Entity):\n",
    "    eid = PrimaryKey(int,auto=True)\n",
    "    salary = Required(int)\n",
    "\n",
    "# Check entity definition\n",
    "show(Emp)\n",
    "\n",
    "# Bind entities to MySQL database\n",
    "db.bind('mysql', host='localhost', user='root', passwd='root', db='emp')\n",
    "\n",
    "# Generate required mappings for entities\n",
    "db.generate_mapping(create_tables=True)\n",
    "\n",
    "# turn on the debug mode\n",
    "sql_debug(True)\n",
    "\n",
    "# Select the records from Emp entities or emp table\n",
    "select(e for e in Emp)[:]\n",
    "\n",
    "# Show the values of all the attribute\n",
    "select(e for e in Emp)[:].show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.3"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {
    "height": "calc(100% - 180px)",
    "left": "10px",
    "top": "150px",
    "width": "259.797px"
   },
   "toc_section_display": true,
   "toc_window_display": true
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
